--** 创建者   : suning
--** 创建日期 : 20231010
--** 功能描述 ：紫金山发运超时
--**************************************************************************
--**************************** 修改日志 ************************************
--**************************************************************************
--** 依赖表：
--**************************************************************************
--*******************  CURRENT_VERSION ： V.1.1  ***************************
drop table jms_dm.dm_waybill_fayun_late_mid_dt;
create external table if not exists jms_dm.dm_waybill_fayun_late_mid_dt(
     order_source_code        string          comment'订单来源编码'
    ,order_source_name        string          comment'订单来源名称'
    ,scan_site_code           string          comment'当前站点编码'
    ,scan_site_name           string          comment'当前站点名称'
    ,scan_site_city_code      string          comment'当前站点所属城市编码'
    ,scan_site_city_name      string          comment'当前站点所属城市名称'
    ,scan_site_provider_code  string          comment'当前站点所属省份编码'
    ,scan_site_provider_name  string          comment'当前站点所属省份名称'
    ,scan_site_agent_code     string          comment'当前站点所属代理区编码'
    ,scan_site_agent_name     string          comment'当前站点所属代理区名称'
    ,late_type                string          comment'超时环节'
    ,duty_type                string          comment'异常类别'
    ,duty_level               string          comment'异常等级'
    ,scale_level              string          comment'规模等级'
    ,today_score              bigint          comment'今日积分'
    ,oper_cnt                 bigint          comment'应操作量'
    ,duty_cnt                 bigint          comment'异常量'
    ,duty_rate                decimal(15,2)   comment'异常率'
    ,peak_cnt                 bigint          comment'峰值件量'
    ,ydlf_36h_cnt             bigint          comment'有到漏发超36h量'
    ,yfwx_36h_cnt             bigint          comment'有发未卸超36h量'
) comment '紫金山发运超时汇总'
partitioned by (dt string comment '分区日期')
stored as parquet
location '/dw/hive/jms_dm.db/external/dm_waybill_fayun_late_mid_dt'
tblproperties (
'discover.partitions'='false',
'parquet.column.index.access'='true'
);


select count(1) as oper_cnt     --异常量
from jms_dm.dm_waybill_fayun_late_detail_dt
where dt = '2023-11-26'

drop table jms_dm.dm_waybill_fayun_late_summary_dt;
create external table if not exists jms_dm.dm_waybill_fayun_late_summary_dt(
     order_source_code        string     comment'订单来源编码'
    ,order_source_name        string     comment'订单来源名称'
    ,scan_site_code           string     comment'当前站点编码'
    ,scan_site_name           string     comment'当前站点名称'
    ,scan_site_city_code      string     comment'当前站点所属城市编码'
    ,scan_site_city_name      string     comment'当前站点所属城市名称'
    ,scan_site_provider_code  string     comment'当前站点所属省份编码'
    ,scan_site_provider_name  string     comment'当前站点所属省份名称'
    ,scan_site_agent_code     string     comment'当前站点所属代理区编码'
    ,scan_site_agent_name     string     comment'当前站点所属代理区名称'
    ,late_type                string     comment'超时环节'
    ,duty_type                string     comment'异常类别'
    ,duty_level               string     comment'异常等级'
    ,scale_level              string     comment'规模等级'
    ,today_score              bigint     comment'今日积分'
    ,total_score              bigint     comment'累积积分'
    ,control_states           string     comment'管控状态'
    ,oper_cnt                 bigint     comment'应操作量'
    ,duty_cnt                 bigint     comment'异常量'
    ,peak_cnt                 bigint     comment'峰值件量'
    ,ydlf_36h_cnt             bigint     comment'有到漏发超36h量'
    ,yfwx_36h_cnt             bigint     comment'有发未卸超36h量'
) comment '紫金山发运超时汇总'
partitioned by (dt string comment '分区日期')
stored as parquet
location '/dw/hive/jms_dm.db/external/dm_waybill_fayun_late_summary_dt'
tblproperties (
'discover.partitions'='false',
'parquet.column.index.access'='true'
);


